#region << 版 本 注 释 >>
/*----------------------------------------------------------------
 * 版权所有 (c) 2025   保留所有权利。
 * CLR版本：4.0.30319.42000
 * 机器名称：DESKTOP-GLJ2Q8J
 * 公司名称：
 * 命名空间：Npoi.ExcelOperate.Standard
 * 唯一标识：047a98c0-4446-441a-9c9c-b2fa337c4c07
 * 文件名：ExcelOperator
 * 当前用户域：DESKTOP-GLJ2Q8J
 * 
 * 创建者： Guo_79991
 * 电子邮箱：799919859@qq.com
 * 创建时间：2025/10/9 21:19:20
 * 版本：V1.0.0
 * 描述：
 *
 * ----------------------------------------------------------------
 * 修改人：
 * 时间：
 * 修改说明：
 *
 * 版本：V1.0.1
 *----------------------------------------------------------------*/
#endregion << 版 本 注 释 >>

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;

namespace Npoi.ExcelOperate.Standard
{
    /// <summary>
    /// Excel操作类,读取Excel文件,获取所有数据.该类为抽象类,不能实例化,只能被继承
    /// </summary>
    public abstract class ExcelOperator
    {
        /// <summary>
        /// 源文件路径
        /// </summary>
        protected string FilePath { get; set; }

        /// <summary>
        /// 文件保存路径
        /// </summary>
        protected string SavePath { get; set; }

        public string GetFilePath()
        {
            return this.FilePath;
        }

        #region 读取相关方法

        /// <summary>
        /// 读取Excel文件
        /// </summary>
        /// <returns>工作簿接口对象</returns>
        public IWorkbook ReadExcel()
        {
            IWorkbook workbook = null;
            if (!File.Exists(FilePath))
            {
                Debug.Print(FilePath + "该文件未能找到,请检查该读入文件是否存在");
                return null;
            }
            string extension = System.IO.Path.GetExtension(FilePath);
            try
            {
                FileStream fs = File.OpenRead(FilePath);
                if (extension.Equals(".xls"))//2007以下
                {
                    workbook = new HSSFWorkbook(fs);
                }
                if (extension.Equals(".xlsx"))//2007以上
                {
                    workbook = new XSSFWorkbook(fs);
                }
                fs.Close();
                return workbook;
            }
            catch (Exception e)
            {
                Debug.Print(e.Message);
                return null;
            }
        }

        /// <summary>
        /// 获取所有数据
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="action">委托传入具体读取逻辑</param>
        /// <returns>List<T></returns>
        /// <exception cref="Exception"></exception> 
        protected List<T> GetAll<T>(Action<ISheet, List<T>> action)
        {
            List<T> list = new List<T>();
            try
            {
                var workbook = ReadExcel();
                if (workbook == null) return null;
                //获得第一个Sheet页
                ISheet sheet = workbook.GetSheetAt(0);
                action.Invoke(sheet, list);//传入操作
                return list;
            }
            catch (Exception ex)
            {
                throw new Exception("Excel读取失败", ex);
            }
        }

        protected List<T> GetAll<T>(Action<IWorkbook, List<T>> action)
        {
            List<T> list = new List<T>();
            try
            {
                var workbook = ReadExcel();
                if (workbook == null) return null;
                action.Invoke(workbook, list);//传入操作
                return list;
            }
            catch (Exception ex)
            {
                throw new Exception("Excel读取失败", ex);
            }
        }

        /// <summary>
        /// 获取Model数据,一般为选型报告表
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="action">委托传入具体读取逻辑</param>
        /// <returns>T</returns>
        /// <exception cref="Exception">Excel没读取成功</exception>
        protected T GetOne<T>(Action<ISheet, T> action) where T : new()
        {
            T model = new T();
            try
            {
                var workbook = ReadExcel();
                if (workbook == null) return default(T);
                //获得第一个Sheet页
                ISheet sheet = workbook.GetSheetAt(0);
                action.Invoke(sheet, model);//传入操作
                return model;
            }
            catch (Exception ex)
            {
                throw new Exception("Excel读取失败", ex);
            }
        }

        protected T GetOne<T>(Action<IWorkbook, T> action) where T : new()
        {
            T model = new T();
            try
            {
                var workbook = ReadExcel();
                if (workbook == null) return default(T);
                action.Invoke(workbook, model);//传入操作
                return model;
            }
            catch (Exception ex)
            {
                throw new Exception("Excel读取失败", ex);
            }
        }

        /// <summary>
        /// 获取Model数据,一般为选型报告表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="action"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        protected T GetValue<T>(Func<ISheet, T> action)
        {
            try
            {
                var workbook = ReadExcel();
                if (workbook == null) return default(T);
                //获得第一个Sheet页
                ISheet sheet = workbook.GetSheetAt(0);
                var model = action.Invoke(sheet);//传入操作
                return model;
            }
            catch (Exception ex)
            {
                throw new Exception("Excel读取失败", ex);
            }
        }

        #endregion

        #region 写入相关方法

        /// <summary>
        /// 写入数据到Excel
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="data">要写入的数据</param>
        /// <param name="action">写入逻辑委托</param>
        /// <returns>是否成功</returns>
        protected bool WriteData<T>(T data, Action<ISheet, T> action)
        {
            try
            {
                return SaveExcelToCopy(workbook =>
                {
                    ISheet sheet = workbook.GetSheetAt(0);
                    action.Invoke(sheet, data);
                });
            }
            catch (Exception ex)
            {
                Debug.Print($"写入数据失败: {ex.Message}");
                return false;
            }
        }

        /// <summary>
        /// 写入列表数据到Excel
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="dataList">要写入的数据列表</param>
        /// <param name="action">写入逻辑委托</param>
        /// <returns>是否成功</returns>
        protected bool WriteListData<T>(List<T> dataList, Action<ISheet, List<T>> action)
        {
            try
            {
                return SaveExcelToCopy(workbook =>
                {
                    ISheet sheet = workbook.GetSheetAt(0);
                    action.Invoke(sheet, dataList);
                });
            }
            catch (Exception ex)
            {
                Debug.Print($"写入列表数据失败: {ex.Message}");
                return false;
            }
        }

        /// <summary>
        /// 在指定位置写入单元格值
        /// </summary>
        /// <param name="sheetIndex">Sheet索引</param>
        /// <param name="rowIndex">行索引</param>
        /// <param name="cellIndex">列索引</param>
        /// <param name="value">要写入的值</param>
        /// <returns>是否成功</returns>
        protected bool WriteCellValue(int sheetIndex, int rowIndex, int cellIndex, object value)
        {
            try
            {
                return SaveExcelToCopy(workbook =>
                {
                    ISheet sheet = workbook.GetSheetAt(sheetIndex);
                    IRow row = sheet.GetRowIfNull(rowIndex);
                    ICell cell = row.GetCellIfNull(cellIndex);

                    SetCellValue(cell, value);
                });
            }
            catch (Exception ex)
            {
                Debug.Print($"写入单元格失败: {ex.Message}");
                return false;
            }
        }

        /// <summary>
        /// 在指定位置写入单元格值（使用源文件）
        /// </summary>
        /// <param name="sheetIndex">Sheet索引</param>
        /// <param name="rowIndex">行索引</param>
        /// <param name="cellIndex">列索引</param>
        /// <param name="value">要写入的值</param>
        /// <returns>是否成功</returns>
        protected bool WriteCellValueToSource(int sheetIndex, int rowIndex, int cellIndex, object value)
        {
            try
            {
                return SaveExcelSourceFile(workbook =>
                {
                    ISheet sheet = workbook.GetSheetAt(sheetIndex);
                    IRow row = sheet.GetRowIfNull(rowIndex);
                    ICell cell = row.GetCellIfNull(cellIndex);

                    SetCellValue(cell, value);
                });
            }
            catch (Exception ex)
            {
                Debug.Print($"写入单元格失败: {ex.Message}");
                return false;
            }
        }

        #endregion

        #region 删除相关方法

        /// <summary>
        /// 删除指定行
        /// </summary>
        /// <param name="sheetIndex">Sheet索引</param>
        /// <param name="rowIndex">要删除的行索引</param>
        /// <returns>是否成功</returns>
        protected bool DeleteRow(int sheetIndex, int rowIndex)
        {
            try
            {
                return SaveExcelToCopy(workbook =>
                {
                    ISheet sheet = workbook.GetSheetAt(sheetIndex);

                    // 检查行是否存在
                    if (rowIndex <= sheet.LastRowNum && sheet.GetRow(rowIndex) != null)
                    {
                        // 使用 NPOI 的 RemoveRow 方法删除行
                        sheet.RemoveRow(sheet.GetRow(rowIndex));

                        // 如果删除的不是最后一行，需要移动后续行
                        if (rowIndex < sheet.LastRowNum)
                        {
                            sheet.ShiftRows(rowIndex + 1, sheet.LastRowNum, -1);
                        }
                    }
                });
            }
            catch (Exception ex)
            {
                Debug.Print($"删除行失败: {ex.Message}");
                return false;
            }
        }

        /// <summary>
        /// 删除指定范围内的行
        /// </summary>
        /// <param name="sheetIndex">Sheet索引</param>
        /// <param name="startRowIndex">起始行索引</param>
        /// <param name="endRowIndex">结束行索引</param>
        /// <returns>是否成功</returns>
        protected bool DeleteRows(int sheetIndex, int startRowIndex, int endRowIndex)
        {
            try
            {
                return SaveExcelToCopy(workbook =>
                {
                    ISheet sheet = workbook.GetSheetAt(sheetIndex);
                    for (int i = startRowIndex; i <= endRowIndex; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row != null)
                        {
                            sheet.RemoveRow(row);
                        }
                    }

                    // 重新组织行索引
                    sheet.ShiftRows(endRowIndex + 1, sheet.LastRowNum, startRowIndex - endRowIndex - 1);
                });
            }
            catch (Exception ex)
            {
                Debug.Print($"删除多行失败: {ex.Message}");
                return false;
            }
        }

        /// <summary>
        /// 删除指定Sheet页
        /// </summary>
        /// <param name="sheetIndex">要删除的Sheet索引</param>
        /// <returns>是否成功</returns>
        protected bool DeleteSheet(int sheetIndex)
        {
            try
            {
                return SaveExcelToCopy(workbook =>
                {
                    if (sheetIndex >= 0 && sheetIndex < workbook.NumberOfSheets)
                    {
                        workbook.RemoveSheetAt(sheetIndex);
                    }
                });
            }
            catch (Exception ex)
            {
                Debug.Print($"删除Sheet页失败: {ex.Message}");
                return false;
            }
        }

        /// <summary>
        /// 清空指定Sheet页的所有数据
        /// </summary>
        /// <param name="sheetIndex">Sheet索引</param>
        /// <returns>是否成功</returns>
        protected bool ClearSheet(int sheetIndex)
        {
            try
            {
                return SaveExcelToCopy(workbook =>
                {
                    ISheet sheet = workbook.GetSheetAt(sheetIndex);
                    for (int i = sheet.LastRowNum; i >= 0; i--)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row != null)
                        {
                            sheet.RemoveRow(row);
                        }
                    }
                });
            }
            catch (Exception ex)
            {
                Debug.Print($"清空Sheet页失败: {ex.Message}");
                return false;
            }
        }

        #endregion

        #region 修改相关方法

        /// <summary>
        /// 修改指定单元格的值
        /// </summary>
        /// <param name="sheetIndex">Sheet索引</param>
        /// <param name="rowIndex">行索引</param>
        /// <param name="cellIndex">列索引</param>
        /// <param name="newValue">新值</param>
        /// <returns>是否成功</returns>
        protected bool UpdateCellValue(int sheetIndex, int rowIndex, int cellIndex, object newValue)
        {
            return WriteCellValue(sheetIndex, rowIndex, cellIndex, newValue);
        }

        /// <summary>
        /// 批量修改单元格值
        /// </summary>
        /// <param name="updates">要修改的单元格集合（Sheet索引, 行索引, 列索引, 新值）</param>
        /// <returns>是否成功</returns>
        protected bool BatchUpdateCells(List<Tuple<int, int, int, object>> updates)
        {
            try
            {
                return SaveExcelToCopy(workbook =>
                {
                    foreach (var update in updates)
                    {
                        int sheetIndex = update.Item1;
                        int rowIndex = update.Item2;
                        int cellIndex = update.Item3;
                        object value = update.Item4;

                        ISheet sheet = workbook.GetSheetAt(sheetIndex);
                        IRow row = sheet.GetRowIfNull(rowIndex);
                        ICell cell = row.GetCellIfNull(cellIndex);

                        SetCellValue(cell, value);
                    }
                });
            }
            catch (Exception ex)
            {
                Debug.Print($"批量修改单元格失败: {ex.Message}");
                return false;
            }
        }

        /// <summary>
        /// 修改行数据
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="sheetIndex">Sheet索引</param>
        /// <param name="rowIndex">行索引</param>
        /// <param name="data">新数据</param>
        /// <param name="action">修改逻辑委托</param>
        /// <returns>是否成功</returns>
        protected bool UpdateRowData<T>(int sheetIndex, int rowIndex, T data, Action<IRow, T> action)
        {
            try
            {
                return SaveExcelToCopy(workbook =>
                {
                    ISheet sheet = workbook.GetSheetAt(sheetIndex);
                    IRow row = sheet.GetRowIfNull(rowIndex);
                    action.Invoke(row, data);
                });
            }
            catch (Exception ex)
            {
                Debug.Print($"修改行数据失败: {ex.Message}");
                return false;
            }
        }

        #endregion

        #region 辅助方法

        /// <summary>
        /// 根据行和列的Index获得单元格中的值
        /// </summary>
        /// <param name="row">行</param>
        /// <param name="cellNum">单元格索引</param>
        /// <returns>单元格中的值</returns>
        protected string GetCellValue(IRow row, int cellIndex)
        {
            return GetCellValueFromCellType(row.GetCell(cellIndex));
        }

        /// <summary>
        /// 根据单元格类型来获取它的值
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <returns>字符串类型的值</returns>
        private string GetCellValueFromCellType(ICell cell)
        {
            if (cell == null)
            {
                return null;
            }
            switch (cell.CellType)
            {
                //首先在NPOI中数字和日期都属于Numeric类型
                //通过NPOI中自带的DateUtil.IsCellDateFormatted判断是否为时间日期类型
                case CellType.Numeric when DateUtil.IsCellDateFormatted(cell):
                    return cell?.DateCellValue.ToString();
                case CellType.Formula:
                case CellType.Numeric:
                    //其他数字类型
                    return cell?.NumericCellValue.ToString();
                //空数据类型
                case CellType.Blank:
                    return "";
                //错误
                case CellType.Error:
                    return null;
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();

                //其他类型都按字符串类型来处理（未知类型CellType.Unknown，字符串类型CellType.String）
                default:
                    return cell?.StringCellValue;
            }
        }

        /// <summary>
        /// 设置单元格值
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <param name="value">值</param>
        private void SetCellValue(ICell cell, object value)
        {
            if (value == null)
            {
                cell.SetCellValue("");
                return;
            }

            switch (value)
            {
                case string str:
                    cell.SetCellValue(str);
                    break;
                case int intVal:
                    cell.SetCellValue(intVal);
                    break;
                case double doubleVal:
                    cell.SetCellValue(doubleVal);
                    break;
                case decimal decimalVal:
                    cell.SetCellValue(Convert.ToDouble(decimalVal));
                    break;
                case float floatVal:
                    cell.SetCellValue(floatVal);
                    break;
                case bool boolVal:
                    cell.SetCellValue(boolVal);
                    break;
                case DateTime dateVal:
                    cell.SetCellValue(dateVal);
                    break;
                default:
                    cell.SetCellValue(value.ToString());
                    break;
            }
        }

        /// <summary>
        /// 保存Excel,该方法传入一个匿名方法
        /// </summary>
        /// <param name="action">具体存入的内容</param>
        /// <returns>是否保存成功</returns>
        protected bool SaveExcelToCopy(Action<IWorkbook> action)
        {
            IWorkbook workbook = null;
            if (string.IsNullOrEmpty(FilePath) || !File.Exists(FilePath))
            {
                Debug.Print("未设置'输出模板文件'或该路径错误");
                return false;
            }

            string extension = Path.GetExtension(FilePath);

            try
            {
                File.Copy(FilePath, SavePath, true);
                FileStream fs = File.Open(SavePath, FileMode.Open, FileAccess.ReadWrite);
                if (extension.Equals(".xls"))//2007以下
                {
                    workbook = new HSSFWorkbook(fs);
                }
                if (extension.Equals(".xlsx"))//2007以上
                {
                    workbook = new XSSFWorkbook(fs);
                }
                action?.Invoke(workbook);
                fs.Close();
                using (var wook = new FileStream(SavePath, FileMode.Create, FileAccess.Write))
                {
                    workbook.Write(wook);
                }
                return true;
            }
            catch (Exception e)
            {
                Debug.Print(e.Message);
                return false;
            }
        }

        protected bool SaveExcelSourceFile(Action<IWorkbook> action)
        {
            IWorkbook workbook = null;
            if (string.IsNullOrEmpty(FilePath) || !File.Exists(FilePath))
            {
                Debug.Print("未设置'文件路径'或该路径错误");
                return false;
            }

            string extension = Path.GetExtension(FilePath);

            try
            {
                FileStream fs = File.Open(FilePath, FileMode.Open, FileAccess.ReadWrite);
                if (extension.Equals(".xls"))//2007以下
                {
                    workbook = new HSSFWorkbook(fs);
                }
                if (extension.Equals(".xlsx"))//2007以上
                {
                    workbook = new XSSFWorkbook(fs);
                }
                action?.Invoke(workbook);
                fs.Close();
                using (var wook = new FileStream(FilePath, FileMode.Create, FileAccess.Write))
                {
                    workbook.Write(wook);
                }
                return true;
            }
            catch (Exception e)
            {
                Debug.Print(e.Message);
                return false;
            }
        }

        #endregion
    }
}